package com.eascs.web.o2o.report.service;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.app.exception.ControllerException;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.doc.excel.ExcelApiHelper;
import com.eascs.common.util.IoUtil;
import com.eascs.notify.email.entity.EmailSourceSetting;
import com.eascs.web.o2o.report.util.BankRowWriteHandler;


@Service
public class BuildExcelService {
	protected final Logger logger =LoggerFactory.getLogger(getClass());
	@Autowired
	EmailSourceSetting emailSourceSetting;
	@Inject
	BuildDataService buildDataService;
	
	
	public String monthDetailReport(Date month) {
		//模板
		String soucreFileName = "/excelTemplate/month_detail_report.xls";
		//输出路径
		StringBuffer s = new StringBuffer();
		String path = emailSourceSetting.getFilepath();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
		String date = sdf.format(month);
		String fileAttach="/月度明细-";
		
		String outPath = s.append(path).append("/"+date).append(fileAttach).append(date).append(".xls").toString();
		
		
		
//sheet1（sheet，col，row都是从0开始）
		//包含的列
		int col = 8;
		//需要统计的列
		int[] arr1 = { 1,2,3,4,5,6,7};
		String title = "O2O金融业务"+date+"月月报-银行明细";
		//开始的表格，行，列
		int sheetIndex=0;
		int rowIndex=4;
		int columnIndex=0;
		List<Object[]> data =buildDataService.getBankData(month); 
		BankRowWriteHandler bankRowWriteHandler1=new BankRowWriteHandler(data, col,arr1,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
//sheet2
		//包含的列
		col = 12;
		//需要统计的列
		int[] arr2 = { 2,3,4,5,7,8,9,10};
		title = "O2O金融业务"+date+"月月报-平台明细";
		//开始的表格，行，列
		sheetIndex=1;
		rowIndex=4;
		columnIndex=0;
		data =buildDataService.getPlatData(month); 
		BankRowWriteHandler bankRowWriteHandler2=new BankRowWriteHandler(data, col,arr2,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		
//sheet3
		//包含的列
		col = 12;
		//需要统计的列
		int[] arr3 = { 3,4,5,6,7,8,9,10,11};
		title = "O2O金融业务"+date+"月月报-省区明细";
		//开始的表格，行，列
		sheetIndex=2;
		rowIndex=2;
		columnIndex=0;
		data =buildDataService.getProvinceData(month); 
		BankRowWriteHandler bankRowWriteHandler3=new BankRowWriteHandler(data, col,arr3,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		
		
		//生产excel
		write(soucreFileName, outPath,bankRowWriteHandler1,bankRowWriteHandler2,bankRowWriteHandler3 );
		
		return outPath;
	}
	
	/*
	 * 生成excel
	 * 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
	 */
	public void write(String soucreFileName, String outPath, BankRowWriteHandler bankRowWriteHandler1,BankRowWriteHandler bankRowWriteHandler2,BankRowWriteHandler bankRowWriteHandler3) {
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName),bankRowWriteHandler1,bankRowWriteHandler2,bankRowWriteHandler3);
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	
	
	
	public String dayDetailReport(Date day) {
		//模板
		String soucreFileName = "/excelTemplate/day_detail_report.xls";
		//输出路径
		StringBuffer s = new StringBuffer();
		String path = emailSourceSetting.getFilepath();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(day);
		String fileAttach="/日报明细-";
		
		String outPath = s.append(path).append("/"+date+"_day").append(fileAttach).append(date).append(".xls").toString();
		
		
		
//sheet1（sheet，col，row都是从0开始）
		//包含的列
		int col = 10;
		//需要统计的列
		int[] arr1 = { 1,2,3,4,5,6,7,8,9};
		String title = "银行放款台账（截止到"+date+"）";
		//开始的表格，行，列
		int sheetIndex=1;
		int rowIndex=4;
		int columnIndex=0;
		List<Object[]> data =buildDataService.getDayBankData(day); 
		BankRowWriteHandler bankRowWriteHandler1=new BankRowWriteHandler(data, col,arr1,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
//sheet2
		//包含的列
		col = 13;
		//需要统计的列
		int[] arr2 = { 1,2,3,6,7,8,9,11,12};
		title = "省区放款台账（截止到"+date+"）";
		//开始的表格，行，列
		sheetIndex=0;
		rowIndex=3;
		columnIndex=0;
		data =buildDataService.getDayProvinceData(day); 
		BankRowWriteHandler bankRowWriteHandler2=new BankRowWriteHandler(data, col,arr2,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		
		
		//生产excel
		writeDay(soucreFileName, outPath,bankRowWriteHandler1,bankRowWriteHandler2 );
		
		return outPath;
	}
	/*
	 * 生成excel
	 * 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
	 */
	public void writeDay(String soucreFileName, String outPath, BankRowWriteHandler bankRowWriteHandler1,BankRowWriteHandler bankRowWriteHandler2) {
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName),bankRowWriteHandler1,bankRowWriteHandler2);
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	
	
	/**
	 * @Title: creditManagerRankReport
	 * @Description: TODO(信贷经理贷款投放统计及排名)
	 * @author lwx
	 * @return
	 * @return String
	 * @throws
	 * @date 2017年2月17日 上午10:34:25
	 */
	public String creditManagerRankReport() {
		//模板
		String soucreFilePath = "/excelTemplate/credit_manager_rank.xls";
		//输出路径
		StringBuffer s = new StringBuffer();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
		Calendar c =Calendar.getInstance();
		c.setTime(new Date());
		c.add(Calendar.MONTH,-1);
		String date = sdf.format(c.getTime());
		String outFilePath = s.append(emailSourceSetting.getFilepath()).append("/"+date+"_managerRank").append("/信贷经理贷款投放统计及排名-").append(date).append(".xls").toString();
		//包含的列
		int col = 18;
		//需要统计的列
		int[] arr1 = {2,3,4,5,6,7,11,12,13,14,15,16};
		String title = "O2O金融2017年信贷经理贷款投放统计及排名";
		String title1="统计时间："+date+"   金额单位：万元"+"                                                               表格制作单位：380金服平台营销部";
		ArrayList<String> list =new ArrayList<String>();
		list.add(title);
		list.add(title1);
		//开始的表格，行，列
		int sheetIndex=0;
		int rowIndex=5;
		int columnIndex=0;
		List<Object[]> data =buildDataService.getCreditManagerRankData(); 
		//生产excel
		writeDay1(soucreFilePath, outFilePath,new BankRowWriteHandler(data, col,arr1,list,sheetIndex,rowIndex,columnIndex));
		return outFilePath;
	}
	
	public void writeDay1(String soucreFileName, String outPath, BankRowWriteHandler bankRowWriteHandler1) {
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName),bankRowWriteHandler1);
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	
	/**
	 * @Title: provinceBusinessRankReport
	 * @Description: TODO(全国各省区贷款投放排名)
	 * @author lwx
	 * @return
	 * @return String
	 * @throws
	 * @date 2017年2月23日 上午10:34:25
	 */
	public String provinceBusinessRankReport() {
		//模板
		String soucreFilePath = "/excelTemplate/province-business-rank.xls";
		//输出路径
		StringBuffer s = new StringBuffer();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
		Calendar c =Calendar.getInstance();
		c.setTime(new Date());
		c.add(Calendar.MONTH,-1);
		String date = sdf.format(c.getTime());
		String outFilePath = s.append(emailSourceSetting.getFilepath()).append("/"+date+"_businessRank").append("/全国各省区贷款投放排名-").append(date).append(".xls").toString();
		//包含的列
		int col = 19;
		//需要统计的列
		int[] arr1 = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15};
		String title = "全国各省区贷款投放排名";
		String title1="统计时间："+date+"                                                                               金额单位：万元";
		ArrayList<String> list =new ArrayList<String>();
		list.add(title);
		list.add(title1);
		//开始的表格，行，列
		int sheetIndex=0;
		int rowIndex=5;
		int columnIndex=0;
		List<Object[]> data =buildDataService.getProvinceBusinessRankData(); 
		//生产excel
		writeDay1(soucreFilePath, outFilePath,new BankRowWriteHandler(data, col,arr1,list,sheetIndex,rowIndex,columnIndex));
		return outFilePath;
	}
	
	
	
	
	
}
